package SpdgBI.Common;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.*;
import java.util.*;

public class DBUtils {
    private static HashMap<String, ComboPooledDataSource> dataSources = new HashMap<String, ComboPooledDataSource>();

    private synchronized static ComboPooledDataSource getDataSource(String dsName) {
        ComboPooledDataSource ds = dataSources.get(dsName);
        if (ds == null) {
            try {
                ds = new ComboPooledDataSource(dsName);
                dataSources.put(dsName, ds);
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException("创建连接池失败：" + dsName);
            }
        }

        return ds;
    }

    public static Connection getConnection(String dsName) {
        ComboPooledDataSource ds = getDataSource(dsName);
        Connection conn = null;
        try {
            conn = ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("获取连接失败：" + dsName);
        }
        return conn;
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }

    public static void DumpMetaData(String SrcTbl, String DstTbl, String PhName) {
        String[] strDataType = {"NUMBER", "NVARCHAR2", "VARCHAR", "NCHAR", "CHAR", "CLOB", "NCLOB", "TIMESTAMP(6)"};
        List<String> lstDataType = new ArrayList<>(Arrays.asList(strDataType));
        List lstTblInfo = null;
        Connection connBI = null;
        Connection connPUBERP = null;
        PreparedStatement pstmtDp = null;
        PreparedStatement pstmtDataSrc = null;
        ResultSet rsDataSrc = null;
        String strSqlDataSrc = "SELECT * FROM " + SrcTbl;
        String strDel = "DELETE FROM " + DstTbl + " WHERE UNITCODE = ?";

        int i;
        try {
            connPUBERP = DBUtils.getConnection(PhName);
            pstmtDataSrc = connPUBERP.prepareStatement(strSqlDataSrc);
            rsDataSrc = pstmtDataSrc.executeQuery();

            connBI = DBUtils.getConnection("SPDGBI");
            connBI.setAutoCommit(false);
            lstTblInfo = Udfs.GetTableInfo(DstTbl);

            pstmtDp = connBI.prepareStatement(strDel);
            pstmtDp.setString(1, PhName);
            pstmtDp.execute();

            pstmtDp = connBI.prepareStatement(((Map) lstTblInfo.get(lstTblInfo.size() - 1)).get("sqlins").toString());

            while (rsDataSrc.next()) {
                pstmtDp.setString(1, PhName);
                for (i = 1; i < lstTblInfo.size() - 1; i++) {
                    //System.out.println(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString() + rsDataSrc.getString("PROJECTID").toString());
                    switch (lstDataType.indexOf(((Map) lstTblInfo.get(i)).get("DATA_TYPE").toString())) {
                        case 0:
                            pstmtDp.setFloat(i + 1, rsDataSrc.getFloat(((Map) lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 1:
                        case 2:
                        case 3:
                        case 4:
                        case 5:
                        case 6:
                            pstmtDp.setString(i + 1, rsDataSrc.getString(((Map) lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 7:
                            pstmtDp.setTimestamp(i + 1, rsDataSrc.getTimestamp(((Map) lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        default:
                            pstmtDp.setObject(i + 1, rsDataSrc.getString(((Map) lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                    }
                }
                pstmtDp.addBatch();
            }
            pstmtDp.executeBatch();
            pstmtDp.clearParameters();
            pstmtDp.clearBatch();
            connBI.commit();

        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            lstTblInfo = null;
            DBUtils.close(connPUBERP);
            DBUtils.close(connBI);
        }

    }

    public static void DumpMetaDataAll(String SrcTbl, String DstTbl) {
        String[] PhList = {"010200", "0104", "010500", "010600","010700", "0109","011000"};
        Long endTime;
        Long StartTime;
        for (int i = 0; i < PhList.length; i++) {
            try {
                StartTime = System.currentTimeMillis();
                DumpMetaData(SrcTbl, DstTbl, PhList[i]);
                endTime = System.currentTimeMillis();
                System.out.println("抽取数据完成，出版单位编码为" + PhList[i] + "的" + SrcTbl + "数据完成，用时：" + (endTime - StartTime));
            } catch (Exception ex) {
                System.out.println("抽取基础数据时出错，抽取出版单位编码为" + PhList[i] + "的" + SrcTbl + "数据没有成功");
            }
        }

    }

    public static void DumpResultSetData(ResultSet rsDataSrc,String DstTbl,String PhName){
        String[] strDataType = {"NUMBER", "NVARCHAR2", "VARCHAR", "NCHAR", "CHAR", "CLOB", "NCLOB", "TIMESTAMP(6)"};
        List<String> lstDataType = new ArrayList<>(Arrays.asList(strDataType));
        List lstTblInfo = null;
        Connection connBI = null;
        PreparedStatement pstmtDp = null;
        String strDel = "DELETE FROM " + DstTbl + " WHERE UNITCODE = ?";
        int i;
        try {

            connBI = DBUtils.getConnection("SPDGBI");
            connBI.setAutoCommit(false);
            lstTblInfo = Udfs.GetTableInfo(DstTbl);

            pstmtDp = connBI.prepareStatement(strDel);
            pstmtDp.setString(1, PhName);
            pstmtDp.execute();

            pstmtDp = connBI.prepareStatement(((Map) lstTblInfo.get(lstTblInfo.size() - 1)).get("sqlins").toString());

            while (rsDataSrc.next()) {
                pstmtDp.setString(1, PhName);
                for (i = 1; i < lstTblInfo.size() - 1; i++) {
                    //System.out.println(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString() + rsDataSrc.getString("PROJECTID").toString());
                    switch (lstDataType.indexOf(((Map) lstTblInfo.get(i)).get("DATA_TYPE").toString())) {
                        case 0:
                            pstmtDp.setFloat(i + 1, rsDataSrc.getFloat(((Map) lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 1:
                        case 2:
                        case 3:
                        case 4:
                        case 5:
                        case 6:
                            pstmtDp.setString(i + 1, rsDataSrc.getString(((Map) lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 7:
                            pstmtDp.setTimestamp(i + 1, rsDataSrc.getTimestamp(((Map) lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        default:
                            pstmtDp.setObject(i + 1, rsDataSrc.getString(((Map) lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                    }
                }
                pstmtDp.addBatch();
            }
            pstmtDp.executeBatch();
            pstmtDp.clearParameters();
            pstmtDp.clearBatch();
            connBI.commit();

        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            lstTblInfo = null;
            DBUtils.close(connBI);
        }

    }

}

